<?php

namespace paper\dao;


use Closure;

/**
 * @property array insertData
 *
 */
class Builder
{

    public array $bindData = [];
    private array $updateData = [];
    private int $bindIndex = 0;
    private string $prefix = "";
    private string $preLogic = '';
    private ?Builder $parent = null;

    const LOGIC_AND = " AND ";
    const LOGIC_OR = " OR ";

    /**
     * @var array 操作属性集合
     *
     * $options['where']  where集合
     * $options['where']  where集合
     * $options['where']  where集合
     * $options['lock']  for update
     * $options['limit'] limit
     * $options['order'] order by
     * $options['group'] group by
     * $options['join'] join
     * $options['alias']
     * $options['raw_update_field']
     */
    protected array $_options = [];
    protected array $options = [
        'table' => null,
        'field' => '*',
        'where' => [],
        'lock' => '',
        'limit' => '',
        'having' => [],
        'order' => [],
        'join' => [],
        'group' => '',
        'alias' => '',
        'cache' => [
            'table' => null,
            'where' => null,
            'having' => null,
            'join' => null,
        ],
        'replace' => false,
    ];

    private ?Builder $unionBuilder = null;
    private ?string $unionKeyword = "union";

    public function __construct(?Builder $builder = null)
    {
        $this->parent = $builder;
    }

    //数据库字段

    final public function saveOptions()
    {
        $this->_options = $this->options;
    }

    final public function removeOptions($options = [])
    {
        foreach ($options as $option) {
            $this->options[$option] = null;
        }
    }

    final public function resumeOptions()
    {
        $this->options = $this->_options;
        unset($this->_options);
    }

    final public function union($table): Builder
    {
        $builder = new self();
        $builder->name($table);
        $builder->unionBuilder = $this;
        return $builder;
    }

    final public function unionAll($table): Builder
    {
        $builder = new self();
        $builder->name($table);
        $builder->unionBuilder = $this;
        $builder->unionKeyword = "union all";
        return $builder;
    }

    /**
     * @param string $prefix
     * @return static
     */
    final public function prefix(string $prefix): static
    {
        $this->prefix = $prefix;
        return $this;
    }


    /**
     * 获取编号
     * @return int
     */
    final public function getBindIndex(): int
    {
        return $this->bindIndex;
    }

    /**
     * 设置参数绑定占位符起始编号
     * 用于子查询 避免冲突
     * @param int $bindIndex
     */
    private function setBindIndex(int $bindIndex): void
    {
        $this->bindIndex = $bindIndex;
    }

    /**
     * 获取参数绑定的占位符
     * @return string
     */
    private function getBindField(): string
    {
        return ':v_' . $this->bindIndex++;
    }

    /**
     * 将数据设置到数据绑定数组中，并且返回绑定的字段名
     * @param $data
     * @return string
     */
    private function setBindData($data): string
    {
        $builder = $this->parent ?: $this;
        $bindFieldKey = $builder->getBindField();
        $builder->bindData[$bindFieldKey] = $data;
        return $bindFieldKey;
    }

    /**
     * 锁定数据 （添加FOR UPDATE）
     * @param bool $lock
     * @return $this
     */
    final public function lock(bool $lock = true): static
    {
        if ($lock) {
            $this->options['lock'] = ' FOR UPDATE';
        }
        return $this;
    }

    /**
     * @return array
     */
    final public function fetchBindData(): array
    {
        return $this->bindData;
    }


    final public function alias($name): static
    {
        $this->options['alias'] = $name;
        return $this;
    }


    final public function table($table): static
    {
        $this->options['table'] = $table;
        return $this;
    }

    final public function name($table, $alias = null): static
    {
        $this->options['alias'] = $alias;
        $this->options['table'] = $table;
        return $this;
    }

    private function buildTableName(): string
    {
        if (!$this->options['cache']['table']) {
            $this->options['cache']['table'] =
                $this->parseTable($this->options['table'], $this->options['alias']);
        }
        return $this->options['cache']['table'];
    }

    final public function parseTable($table, $alias = null): string
    {
        if (is_array($table)) {
            [$table, $alias] = $table;
        }
        if ($table instanceof Builder) {
            return $table->buildSubSql($alias);
        } else if ($table instanceof Closure) {
            return $this->subQuery($table)->buildSubSql($alias);
        }
        return '`' . $this->prefix . $table . '`' . ($alias ? ' as ' . $alias : ' ');
    }


    final public function limit($offset, int $limit = NULL): static
    {
        if ($offset === false) {
            $this->options['limit'] = '';
            return $this;
        }
        if ($limit) {
            $this->options['limit'] = " LIMIT " . intval($offset) . "," . $limit;
        } else {
            $this->options['limit'] = " LIMIT " . intval($offset);
        }

        return $this;
    }

    /**
     * 设置排序
     * 需要 注意的是，此方法并不会使用参数绑定防止注入
     * 这意味着此函数绝不能直接传入未经过滤或处理的用户输入
     * @param $str
     * @return $this
     */
    final public function order($str): static
    {
        $this->options['order'][] = $str;
        return $this;
    }

    /**
     * 设置排序
     * @param $str
     * @return $this
     */
    final public function group($str): static
    {
        if ($str) {
            $this->options['group'] = ' GROUP BY ' . $str . ' ';
        }
        return $this;
    }

    /**
     * 设置自增
     * @param     $field
     * @param int|float $value
     * @return static
     */
    final public function inc($field, int|float $value = 1): static
    {
        $fieldKey = $this->setBindData($value);
        $this->options['raw_update_field'][] = "`$field` = `$field` + $fieldKey";
        return $this;
    }

    /**
     * 设置自减
     * @param     $field
     * @param int $value
     * @return static
     */
    final public function dec($field, int $value = 1): static
    {
        $fieldKey = $this->setBindData($value);
        $this->options['raw_update_field'][] = "`$field` = `$field` - $fieldKey";
        return $this;
    }

    /**
     * 联合查询
     * @param        $table
     * @param string $on
     * @param string $joinType
     * @return $this
     */
    final public function join($table, string $on = '', string $joinType = 'left'): static
    {
        $this->options['join'][] = [$joinType, $on, $table];
        return $this;
    }

    private function buildOrder(): string
    {
        $order = $this->options['order'] ?? [];
        if (count($order) > 0) {
            return ' ORDER BY ' . implode(",", $order);
        }
        return '';
    }


    private function buildJoin(): string
    {
        if (!$this->options['cache']['join']) {
            $joinStr = "";
            $joins = $this->options['join'] ?? [];
            foreach ($joins as $join) {
                [$type, $on, $table] = $join;
                if (is_array($table)) {
                    $table = $this->parseTable($table[0], $table[1]);
                } else {
                    $table = $this->parseTable($table);
                }
                $on = $on ? 'ON ' . $on : '';
                $joinStr .= ' ' . $type . ' JOIN ' . $table . ' ' . $on;
            }
            $this->options['cache']['join'] = $joinStr;
        }
        return $this->options['cache']['join'];
    }

    /**
     * 设置查询条件
     * @param      $field
     * @param null $op
     * @param null $condition
     * @return static
     */
    final public function where($field = null, $op = null, $condition = null): static
    {
        if ($field)
            $this->addWhereExp(self::LOGIC_AND, $field, $op, $condition);
        return $this;
    }

    /**
     * 设置查询条件
     * @param      $field
     * @param null $op
     * @param null $condition
     * @return static
     */
    final public function whereOr($field, $op = null, $condition = null): static
    {
        $this->addWhereExp(self::LOGIC_OR, $field, $op, $condition);
        return $this;
    }

    /**
     * 设置查询条件
     * @param $id
     * @return static
     */
    final public function id($id): static
    {
        $this->addWhereExp(null, 'id', '=', $id);
        return $this;
    }

    /**
     * @return $this
     */
    final public function having($field = null, $op = null, $condition = null): static
    {
        if ($field)
            $this->addHavingExp(self::LOGIC_AND, $field, $op, $condition);
        return $this;
    }

    /**
     * @return $this
     */
    final public function havingOr($field = null, $op = null, $condition = null): static
    {
        if ($field)
            $this->addHavingExp(self::LOGIC_OR, $field, $op, $condition);
        return $this;
    }


    final public function or(): static
    {
        $this->preLogic = self::LOGIC_OR;
        return $this;
    }

    final public function and(): static
    {
        $this->preLogic = self::LOGIC_AND;
        return $this;
    }

    /**
     * 添加一个where表达式
     * @param string $logic 组内连接的逻辑符号
     * @param null $field
     * @param null $op
     * @param null $value
     */
    private function addWhereExp(string $logic, $field = null, $op = null, $value = null): void
    {
        $this->options['where'][] = [$this->preLogic, $logic, $field, $op, $value];
        $this->and();
    }

    /**
     * 添加一个where表达式
     * @param string $logic 组内连接的逻辑符号
     * @param null $field
     * @param null $op
     * @param null $value
     */
    private function addHavingExp(string $logic, $field = null, $op = null, $value = null)
    {
        $this->options['having'][] = [$this->preLogic, $logic, $field, $op, $value];
        $this->and();
    }

    /**
     * 构建 where 条件字符串
     * @return string
     */
    private function buildWhere(): string
    {
        $conditions = $this->buildConditions('where');
        if (!$conditions)
            return '';
        return ' WHERE ' . $conditions;
    }

    /**
     * 构建 where 条件字符串
     * @return string
     */
    private function buildHaving(): string
    {
        $conditions = $this->buildConditions('having');
        if (!$conditions)
            return '';
        return ' HAVING ' . $conditions;
    }

    /**
     * @param string $operator 可选where和having
     * @return string
     */
    private function buildConditions(string $operator): string
    {
        if (!$this->options['cache'][$operator]) {
            $conditions = '';
            foreach ($this->options[$operator] as $whereExp) {
                /**
                 * @var $prefix string 组间逻辑符号
                 * @var $logic string 组内逻辑符号
                 * @var $field mixed 字段、组内条件
                 * @var $op mixed 操作符号
                 * @var $value mixed $field为字段时，此字段为值
                 */
                [$prefix, $logic, $field, $op, $value] = $whereExp;
                $conditions .= $prefix;
                if ($field instanceof Closure) {
                    $conditions = '(' . $this->subQuery($field)->buildConditions($operator) . ')';
                } elseif (is_array($field)) {
                    $where = [];
                    foreach ($field as $key => $value) {
                        if (is_array($value)) {
                            [$field, $op, $val] = $value;
                        } else {
                            $field = $key;
                            $op = '=';
                            $val = $value;
                        }
                        //支持aa.bb这类的格式
                        if (str_contains($field, '.')) {
                            $tmp = explode(".", $field);
                            $field = '`' . $tmp[0] . '`.`' . $tmp[1] . '`';
                        } else
                            $field = '`' . $field . '`';
                        $where[] = $field . $op . ' ' . $this->buildValue($op, $val);
                    }
                    $conditions .= implode($logic, $where);

                } elseif (is_string($field) && is_string($op)) {
                    $conditions .= '`' . $field . '`' . $op . $this->buildValue($op, $value);
                } elseif (is_string($field) && $op == null) {
                    $conditions .= $field;
                }
            }
            $this->options[$operator] = [];
            $this->options['cache'][$operator] = $conditions;
        }
        return $this->options['cache'][$operator];
    }

    /**
     * 构建值
     * @param $op
     * @param $condition
     * @return string
     */
    private function buildValue($op, $condition): string
    {
        $op = strtolower($op);
        if (str_contains($op, 'in') && is_array($condition)) {
            $inKeys = [];
            $condition = array_unique($condition);
            foreach ($condition as $value) {
                $fieldKey = $this->setBindData($value);
                $inKeys[] = $fieldKey;
            }
            return '(' . implode(',', $inKeys) . ')';
        } else if (str_contains($op, 'between') && is_array($condition)) {
            $fieldKey1 = $this->setBindData($condition[0]);
            $fieldKey2 = $this->setBindData($condition[1]);
            return $fieldKey1 . ' AND ' . $fieldKey2;
        } else if ($condition instanceof Closure) {
            return $this->subQuery($condition)->buildSubSql();
        }
        return $this->setBindData($condition);
    }

    /**
     * 开启一个子查询 返回子查询的Builder
     * @param Closure $closure
     * @return Builder
     */
    private function subQuery(Closure $closure): Builder
    {
        /**
         * 实例化一个Builder作为子构建类
         * 同时传入当前Builder作为父构建类
         */
        $builder = new self($this);
        //设置子查询的字段起始索引
        //执行闭包
        $closure($builder);
        return $builder;
    }

    /**
     * 单独设置需要更新的数据源
     * @param $data
     * @return $this
     */
    final public function data($data): static
    {
        $this->updateData = $data;
        return $this;
    }


    /**
     * 构建子查询SQL并且返回
     * @param null $alias
     * @return string
     */
    final public function buildSubSql($alias = null): string
    {
        return '(' . $this->buildSelectSql() . ')' . ($alias ? ' as ' . $alias : '');
    }


    final public function bindData(array $bindData = []): static
    {
        $builder = $this->parent ?: $this;
        foreach ($bindData as $key => $value) {
            $builder->bindData[':' . $key] = $value;
        }
        return $this;
    }


    /**
     * 构建update SQL语句
     * @param string|array $data 要修改的数据
     * @return string
     */
    final public function buildUpdateSql(string|array $data = []): string
    {
        //处理单独传入的数据

        $updateFields = $this->options['raw_update_field'] ?? [];

        if (is_string($data)) {
            $field = $data;
        } else {
            $data = array_merge($this->updateData, $data);
            foreach ($data as $key => $value) {
                $fieldKey = $this->setBindData($value);
                $updateFields[] = '`' . $key . '`=' . $fieldKey;
            }
            $field = implode(",", $updateFields);
        }

        $table = $this->buildTableName();
        $join = $this->buildJoin();
        $where = $this->buildWhere();
        $order = $this->buildOrder();
        $limit = $this->options['limit'] ?? '';

        return sprintf('UPDATE %s SET %s%s%s%s%s', $table, $field, $join, $where, $order, $limit);
    }


    /**
     * 构建insert SQL语句
     * @param array $data 要插入的数据
     * @return string
     */
    final public function buildInsertSql(array $data = []): string
    {
        $fields = [];
        $data = array_merge($this->updateData, $data);
        foreach ($data as $key => $value) {
            $fieldKey = $this->setBindData($value);
            $fields[] = '`' . $key . '` = ' . $fieldKey;
        }
        $fieldsStr = implode(',', $fields);
        return ($this->options['replace'] ? 'REPLACE INTO' : 'INSERT INTO') . ' ' . $this->buildTableName() . ' SET ' . $fieldsStr;
    }


    /**
     * 构建DELETE SQL语句
     * 删除记录
     * @return string
     */
    final public function buildDeleteSql(): string
    {
        $tableName = $this->buildTableName();
        $order = $this->buildOrder();
        $limit = $this->options['limit'] ?? '';
        $where = $this->buildWhere();

        return sprintf('DELETE FROM %s%s%s%s', $tableName, $where, $order, $limit);
    }

    private function buildUnionSql(): string
    {
        if (!$this->unionBuilder)
            return '';
        $unionSql = $this->unionBuilder->buildSelectSql() . ' ' . $this->unionKeyword . ' ';
        $this->setBindIndex($this->unionBuilder->getBindIndex());
        $this->bindData = array_merge($this->bindData, $this->unionBuilder->fetchBindData());
        return $unionSql;
    }

    final public function buildSelectSql(): string
    {

        //如果有联合查询则先构建联合查询的语句
        $unionSql = $this->buildUnionSql();

        $table = $this->buildTableName();
        $join = $this->buildJoin();
        $where = $this->buildWhere();
        $field = $this->buildField();
        $having = $this->buildHaving();
        $order = $this->buildOrder();
        $group = $this->options['group'] ?? '';
        $limit = $this->options['limit'] ?? '';
        $lock = $this->options['lock'] ?? '';

        return sprintf('%sSELECT %s FROM %s%s%s%s%s%s%s%s',
            $unionSql,
            $field,
            $table,
            $join,
            $where,
            $group,
            $having,
            $order,
            $limit,
            $lock);
    }

    /**
     * 构建快捷聚合查询语句
     * @param $function
     * @param string $field
     * @param string $alias
     * @return string
     */
    final public function buildAggregateSql($function, string $field = "*", string $alias = "_AGGREGATE_"): string
    {
        if ($this->options['group']) {
            return (new self())->name($this, "_AGGREGATE_TABLE")->buildAggregateSql($function, $field, $alias);
        } else {
            $this->field($function . "(" . $field . ") as " . $alias);
            return $this->buildSelectSql();
        }
    }


    /**
     * @param $field string
     * @return static
     */
    final public function field(string $field): static
    {
        $this->options['field'] = $field;
        return $this;
    }

    final public function getField()
    {
        return $this->options['field'];
    }

    final public function filter($fields): static
    {
        $this->options['filter'] = $fields;
        return $this;
    }

    private function buildField(): string
    {
        if (is_string($this->options['field']))
            return $this->options['field'];
        return implode(",", $this->options['field']);
    }

}